﻿drop database BanSach
create database BanSach
use BanSach
drop table DocGia
create table tblUser
(ID nvarchar(200) ,pwd nvarchar(200) )
create table DocGia (MaDG nvarchar(200)  constraint MADG primary key, TenDG nvarchar(200),NgaySinh date,DiaChi nvarchar(200))

drop table HoaDon

create table HoaDon (MaHD nvarchar(200) constraint MAHD not null primary key, MaDG nvarchar(200) foreign key references DocGia(MADG),MaSach nvarchar(200) foreign key references Sach(MAS),MaNV nvarchar(200) foreign key references NhanVien(MANV),NgayMuon date,NgayTra date)

drop table Sach

create table Sach (MaS nvarchar(200) constraint MAS not null primary key ,TenS nvarchar(200),Gia nvarchar(200),NXB nvarchar(200),NamXB nvarchar(200),SL nvarchar(200), SlMuon nvarchar(200))

drop table NhanVien

create table NhanVien(MaNV nvarchar(200) constraint MANV not null primary key,TenNV nvarchar(200),DiaChi nvarchar(200))

insert into DocGia values ('001','Lê Xuân Thịnh','1993/10/15','Đông Anh Hà Nội')
insert into DocGia values ('002','Vũ Chí Trung','1993/10/10','Hà Nam')
insert into DocGia values ('003','Cao Minh Ngọc','1993/2/9','Hải Dương')
insert into DocGia values ('004','Nguyễn Thành Trung','1993/10/1','Hà Nội')
insert into DocGia values ('005','Nguyễn Anh Tuấn','1993/1/10','Hà Nội')
insert into DocGia values ('006','Phạm Thị Ngọc','1993/3/10','Hà Nội')
insert into DocGia values ('007','Đỗ Ngọc Minh','1993/6/26','Hà Nội')
insert into DocGia values ('008','Trần Thế Hồng','1990/8/10','Hải Dương')
insert into DocGia values ('009','Nguyễn Thị Xoa','1993/6/10','Hà Nội')
insert into DocGia values ('010','Nguyễn Thành Luân','1993/4/10','Sóc Sơn Hà Nội')

insert into HoaDon values ('001','001','001','001','2014/5/10','2014/5/20')

insert into Sach values ('001','Lập Trình C#','Nhà XB Giáo Dục','Giáo Dục','2014','10','1')
insert into Sach values ('002','Visual Basic','Nhà XB Giáo Dục','Giáo Dục','2014','10','1')
insert into Sach values ('003','HTML cơ bản','Nhà XB Giáo Dục','Giáo Dục','2014','10','1')

insert into NhanVien values('001','Lê Xuân Thinh','Đông Anh Hà Nội')
insert into NhanVien values('002','Vũ Chí Trung','Hà Nam')


select * from tblUser
insert into tblUser values ('lethinh','thinh123')
delete from tblUser

create proc user_them
@ID nvarchar(200),
@Pwd nvarchar(200)
as
begin
insert into tblUser values (@ID,@Pwd)
end
go
create proc nhanvien_them
@manv nvarchar(200),
@tennv nvarchar(200),
@diachi nvarchar(200)
as
begin 
insert into NhanVien values(@manv,@tennv,@diachi)
end
go
create proc nhanvien_sua
@manv nvarchar(200),
@tennv nvarchar(200),
@diachi nvarchar(200)
as
begin
update NhanVien set MaNV =@manv,TenNV = @tennv,DiaChi = @diachi where MaNV =@manv
end
go
create proc  nhanvien_xem
@manv nvarchar(200),
@tennv nvarchar(200),
@diachi nvarchar(200)
as
begin
select * from NhanVien where MaNV = @manv
end
go
create proc nhanvien__xoa
@manv nvarchar(200)
as
begin
delete NhanVien where MaNV = @manv
end
go
create proc user_xem
@ID nvarchar(200),
@Pwd nvarchar(200)
as
begin
select * from tblUser where ID = @ID and pwd =@Pwd
end
go
create proc user_sua
@ID nvarchar(200),
@Pwd nvarchar(200)
as
begin
update tblUser set pwd = @Pwd where ID = @ID
end
go

create proc user_xoa
@ID nvarchar(200)
as
begin
delete from tblUser where ID = @ID
end
go